package com.jiuwei.commons.mysql2word.mysql;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.fasterxml.jackson.databind.util.JSONPObject;
import com.jiuwei.commons.mysql2word.utils.JsonUtil;
import jdk.nashorn.internal.parser.JSONParser;
import org.apache.log4j.Logger;

import com.jiuwei.commons.mysql2word.kit.StringKit;
import com.mysql.jdbc.StringUtils;

public class MysqlKit extends MysqlConfig {
	private final static Logger logger = Logger.getLogger(MysqlKit.class);
	private static List<MysqlTable> tableList = null;

	public static List<MysqlTable> getTableInfos(String dbType) {
		logger.debug("starting to get table info...");
		if (tableList == null) {
			initTableInfos(dbType);
		}
		return tableList;
	}

	private static void initTableInfos(String dbType) {
		tableList = new ArrayList<MysqlTable>();
		try {
//			ResultSet tableResultSet = dbmd.getTables(null, "%", "%",new String[] { "TABLE" });// 表数据集
			Statement statement = dbConnection.createStatement();
			ResultSet tableResultSet=null;
			if("mysql".equals(dbType)){
			 tableResultSet =statement.executeQuery(" SELECT  "
			+" TABLE_SCHEMA AS TABLE_CAT, "
			+" 		NULL AS TABLE_SCHEM, "
			+" 		TABLE_NAME, "
			+" 		CASE "
			+" WHEN TABLE_TYPE = 'BASE TABLE' THEN "
			+" 		CASE "
			+" WHEN TABLE_SCHEMA = 'mysql' "
			+" OR TABLE_SCHEMA = 'performance_schema' THEN "
			+" 'SYSTEM TABLE' "
			+" ELSE "
			+" 'TABLE' "
			+" END "
			+" WHEN TABLE_TYPE = 'TEMPORARY' THEN "
			+" 'LOCAL_TEMPORARY' "
			+" ELSE "
			+" 		TABLE_TYPE "
			+" END AS TABLE_TYPE, "
			+" 		TABLE_COMMENT AS REMARKS, "
			+" NULL AS TYPE_CAT, "
			+" 		NULL AS TYPE_SCHEM, "
			+" NULL AS TYPE_NAME, "
			+" 		NULL AS SELF_REFERENCING_COL_NAME, "
			+" NULL AS REF_GENERATION "
			+" 		FROM "
			+" INFORMATION_SCHEMA. TABLES "
			+" 		WHERE "
			+" TABLE_SCHEMA = '"+DATABASE+"' ");
			} else if("pg".equals(dbType)){

			 tableResultSet = statement.executeQuery("SELECT "
					+ "table_catalog AS TABLE_CAT, "
					+ "null AS TABLE_SCHEM, "
					+ "table_name, "
					+ "'TABLE' AS TABLE_TYPE, "
					+ " obj_description(('public.' || table_name)::regclass)   AS REMARKS, "
					+ "null AS TYPE_CAT, "
					+ "null AS TYPE_SCHEM, "
					+ "null AS TYPE_NAME, "
					+ "null AS SELF_REFERENCING_COL_NAME, "
					+ "null AS REF_GENERATION "
					+ "FROM information_schema.tables "
					+ "WHERE table_schema = 'public';");

			}


			MysqlTable mysqlTable = null;

			// gsms_msg_ticket_sms_1231
			while (tableResultSet.next()) {
				String tableName = tableResultSet.getString("TABLE_NAME");
				String tableremarks = tableResultSet.getString("REMARKS");
				if (tableName.contains("0")||tableName.contains("1")){
					continue;
				}
				if (!StringUtils.isNullOrEmpty(tableName)) {
					ResultSet rs = dbmd.getColumns(null, "%", tableName, "%");
					mysqlTable = new MysqlTable(tableName,tableremarks);
					mysqlTable.setTableName(tableName);
					while (rs.next()) {
						for (String fieldName : tableRelation.keySet()) {
							String fieldValue = rs.getString(fieldName);
							if (!StringUtils.isNullOrEmpty(fieldValue) && fieldValue.equals("id")) {
//								mysqlTable.setTitle(mysqlTable.getTitle() + rs.getString("REMARKS"));
								mysqlTable.setTitle(mysqlTable.getTitle());
							}
							mysqlTable.addSingleField(rs.getString(fieldName));
						}

					}
					tableList.add(mysqlTable);
				}// end if

			}// end while

			if (IS_ADD_TABLE_INDEX)
				addIndexInfo();

		} catch (Exception e) {
			logger.error("获取数据库表信息失败>>>", e);
		}
	}

	// 添加索引信息(有BUG，待优化)
	public static void addIndexInfo() throws Exception {
		ResultSet rs = null;
		for (MysqlTable mysqlTable : tableList) {
			rs = dbmd.getIndexInfo(null, null, mysqlTable.getTableName(),
					false, false);
			for (String fieldName : tableIndex.keySet()) {
				List<String[]> fieldList = new ArrayList<String[]>(mysqlTable
						.getFieldList().size());
				Collections.copy(fieldList, mysqlTable.getFieldList());
				for (String[] fieldValues : fieldList) {
					logger.debug(mysqlTable.getCursor());
					mysqlTable.setCursor(mysqlTable.getCursor());
					mysqlTable.setFieldValues(fieldValues);
					while (rs.next()) {
						String columnName = rs.getString("Column_name");
						if (StringKit.isExistInArray(fieldValues, columnName) != null) {
							mysqlTable.addSingleField(rs.getString(fieldName));
						} else {
							mysqlTable.addSingleField("");
						}
					}
					// mysqlTable.getFieldList().remove(fieldValues);
				}
			}

		}
	}

	public static void consoleInfo() {
		// 输出所有表信息
		for (MysqlTable table : tableList) {
			logger.debug("表名：" + table.getTitle());
			for (String fieldTile : tableInfo.values()) {
				logger.debug(fieldTile + "	\n");
			}

			for (String[] fieldValues : table.getFieldList()) {
				for (String fieldValue : fieldValues) {
					logger.debug(fieldValue + " 	\n");
				}
				logger.debug("---");
			}
		}
	}

}
